﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using OutPutPermissions_App;

namespace ConsoleAppOutPut_TestData
{
    public struct Data_Result
    {
        public string 样品序号 { get; set; }
        public string 报告编号 { get; set; }
        public string 样品名称 { get; set; }
        public string 受检单位 { get; set; }
        public string 厂家单位 { get; set; }
        public string 规格 { get; set; }
        public string 凝结时间_初凝 { get; set; }
        public string 凝结时间_终凝 { get; set; }
        public string 安定性 { get; set; }
        public string 抗折强度3天 { get; set; }
        public string 抗折强度28天 { get; set; }
        public string 抗压强度3天 { get; set; }
        public string 抗压强度28天 { get; set; }

    }
    class Program
    {
        static void Main(string[] args)
        {
            DBUtil.DBSession db = new DBUtil.DBSession(DBUtil.DataBaseType.MsSql);

            string init_sql = @"select p.id 样品序号,psn.SelectNO 报告编号,p.ProductName 样品名称,dbo.getAllStandardTag(p.id,',') 规格,tu1.Name 受检单位,tu2.Name as 厂家单位 
{0} from Product p
left join ProductSelectNO psn on p.ID = psn.Product_ID left join TrustUnit tu1 on tu1.ID = p.BeCheckUnit_ID left join TrustUnit tu2 on tu2.ID = p.FactoryUnit_ID where 1=1 ";

            List<int> lstYear = new List<int>()
            {
                2015,2016
            };

            var dictLike = new Dictionary<string, string>();
            dictLike.Add("and psn.SelectNO like '%JCJW{0}HNT%' and p.ProductName like '%水泥%' order by p.id asc", "水泥");
            dictLike.Add("and psn.SelectNO like '%JCJW{0}HNT%' and p.ProductName like '%砂%' order by p.id asc", "砂");
            dictLike.Add("and psn.SelectNO like '%JCJW{0}HNT%' and p.ProductName like '%石%' order by p.id asc", "石");
            dictLike.Add("and psn.SelectNO like '%JCJW{0}HNT%' and p.ProductName like '%灰%' order by p.id asc", "粉煤灰");
            dictLike.Add("and psn.SelectNO like '%JCJW{0}HNT%' and p.ProductName like '%矿%' order by p.id asc", "矿粉");
            dictLike.Add("and psn.SelectNO like '%JCJW{0}HNT%' and p.ProductName like '%剂%' order by p.id asc", "剂");


            foreach (int year in lstYear)
            {
                DataSet ds = new DataSet();

                string sheetName = year + "JCJW实验结果";

                foreach (var kv in dictLike)
                {
                    string type = kv.Value;
                    string whSql = string.Format(kv.Key, year);
                    var pair = GetType(type);
                    string qy_sql = string.Format(init_sql, pair.Key) + whSql;
                    DataTable data = db.QueryTable(qy_sql, 30000);


                    ds.Tables.Add(refData(data, type));
                }


                string path = Path.GetFullPath("testdata");
                if (!Directory.Exists(path))
                {
                    Directory.CreateDirectory(path);
                }
                path = Path.Combine(path, sheetName + ".xlsx");
                bool isOk = NPOIHelper.ToExcel(ds, path);
            }


        }

        static KeyValuePair<string, string> GetType(string type)
        {
            string key = string.Empty, val = string.Empty;
            if (type == "水泥")
            {
                key = @",'' as 凝结时间_初凝,'' as 凝结时间_终凝,'' as 安定性,
                '' as 抗折强度3天,'' as 抗折强度28天,'' as 抗压强度3天,'' as 抗压强度28天 ";
                val = @"case 项目
                when '凝结时间,min-初凝' then '凝结时间_初凝'
                when '凝结时间,min-终凝' then '凝结时间_终凝'
                when '安定性（雷氏夹沸煮后增加距离）,mm' then '安定性'
                when '抗折强度（3d）,MPa' then '抗折强度3天'
                when '抗折强度（28d）,MPa' then '抗折强度28天'
                when '抗压强度（3d）,MPa' then '抗压强度3天'
                when '抗压强度（28d）,MPa' then '抗压强度28天'
                end as 项目,";
            }
            else if (type == "砂")
            {
                key = @",'' as 颗粒级配_公称粒径5_00mm方孔筛累计筛余
,'' as 颗粒级配_公称粒径2_50mm方孔筛累计筛余
,'' as 颗粒级配_公称粒径1_25mm方孔筛累计筛余
,'' as 颗粒级配_公称粒径630μm方孔筛累计筛余
,'' as 颗粒级配_公称粒径315μm方孔筛累计筛余
,'' as 颗粒级配_公称粒径160μm方孔筛累计筛余
,'' as 细度模数
,'' as 含泥量
,'' as MB值
,'' as 泥块含量";

                val = @"case 项目
when '颗粒级配-公称粒径5.00mm方孔筛累计筛余,%' then '颗粒级配_公称粒径5_00mm方孔筛累计筛余'
when '颗粒级配-公称粒径2.50mm方孔筛累计筛余,%' then '颗粒级配_公称粒径2_50mm方孔筛累计筛余'
when '颗粒级配-公称粒径1.25mm方孔筛累计筛余,%' then '颗粒级配_公称粒径1_25mm方孔筛累计筛余'
when '颗粒级配-公称粒径630μm方孔筛累计筛余,%' then '颗粒级配_公称粒径630μm方孔筛累计筛余'
when '颗粒级配-公称粒径315μm方孔筛累计筛余,%' then '颗粒级配_公称粒径315μm方孔筛累计筛余'
when '颗粒级配-公称粒径160μm方孔筛累计筛余,%' then '颗粒级配_公称粒径160μm方孔筛累计筛余'
when '细度模数' then '细度模数'
when '含泥量,%' then '含泥量'
when 'MB值' then 'MB值'
when '泥块含量,%' then '泥块含量'
                end as 项目,";
            }
            else if (type == "石")
            {
                key = @",'' as 颗粒级配5mm_25mm_31_5mm方孔筛累计筛余
,'' as 颗粒级配5mm_25mm_26_5mm方孔筛累计筛余
,'' as 颗粒级配5mm_25mm_19_0mm方孔筛累计筛余
,'' as 颗粒级配5mm_25mm_16_0mm方孔筛累计筛余
,'' as 颗粒级配5mm_25mm_9_5mm方孔筛累计筛余
,'' as 颗粒级配5mm_25mm_4_75mm方孔筛累计筛余
,'' as 颗粒级配5mm_25mm_2_36mm方孔筛累计筛余
,'' as 针_片状颗粒含量
,'' as 含泥量
,'' as 泥块含量
,'' as 压碎值指标
";
                val = @"case 项目
when '颗粒级配5mm～25mm-31.5mm方孔筛累计筛余,%' then '颗粒级配5mm_25mm_31_5mm方孔筛累计筛余'
when '颗粒级配5mm～25mm-26.5mm方孔筛累计筛余,%' then '颗粒级配5mm_25mm_26_5mm方孔筛累计筛余'
when '颗粒级配5mm～25mm-19.0mm方孔筛累计筛余,%' then '颗粒级配5mm_25mm_19_0mm方孔筛累计筛余'
when '颗粒级配5mm～25mm-16.0mm方孔筛累计筛余,%' then '颗粒级配5mm_25mm_16_0mm方孔筛累计筛余'
when '颗粒级配5mm～25mm-9.5mm方孔筛累计筛余,%' then '颗粒级配5mm_25mm_9_5mm方孔筛累计筛余'
when '颗粒级配5mm～25mm-4.75mm方孔筛累计筛余,%' then '颗粒级配5mm_25mm_4_75mm方孔筛累计筛余'
when '颗粒级配5mm～25mm-2.36mm方孔筛累计筛余,%' then '颗粒级配5mm_25mm_2_36mm方孔筛累计筛余'
when '针、片状颗粒含量,%' then '针_片状颗粒含量'
when '含泥量,%' then '含泥量'
when '泥块含量,%' then '泥块含量'
when '压碎值指标,%' then '压碎值指标'
                end as 项目,";
            }
            else if (type == "粉煤灰")
            {
                key = @",'' as 细度
,'' as 需水量比
,'' as 烧失量 ";
                val = @"case 项目
when '细度,%' then '细度'
when '需水量比,%' then '需水量比'
when '烧失量,%' then '烧失量'
                end as 项目,";
            }
            else if (type == "矿粉")
            {
                key = @",'' as 流动度比
,'' as 比表面积
,'' as 氯离子
,'' as 烧失量
,'' as 活性指数7d
,'' as 活性指数28d";

                val = @"case 项目
            when '流动度比,%' then '流动度比'
when '比表面积,/kg' then '比表面积'
when '氯离子,%' then '氯离子'
when '烧失量,%' then '烧失量'
when '活性指数（7d）,%' then '活性指数7d'
when '活性指数（28d）,%' then '活性指数28d'

                end as 项目,";
            }
            else if (type == "剂")
            {
                key = @",'' as pH值
,'' as 密度
,'' as 氯离子含量
,'' as 凝结时间之差_初凝
,'' as 凝结时间之差_终凝
,'' as 减水率
,'' as 引入混凝土的氯离子含量
,'' as 项目1";
                val = @"case 项目
when 'pH值' then 'pH值'
when '密度,g/c' then '密度'
when '氯离子含量,％' then '氯离子含量'
when '凝结时间差,min-初凝' then '凝结时间之差_初凝'
when '凝结时间差,min-终凝' then '凝结时间之差_终凝'
when '减水率,％' then '减水率'
when '引入混凝土的氯离子含量,kg/' then '引入混凝土的氯离子含量'
when '项目1' then '项目1'
                end as 项目,";
            }
            return new KeyValuePair<string, string>(key, val);
        }


        static DataTable refData(DataTable data, string sheetName)
        {

            DBUtil.DBSession db = new DBUtil.DBSession(DBUtil.DataBaseType.MsSql);

            DataTable dataNew = data.Copy();
            List<int> lstIndex = new List<int>();
            int j = 0;
            foreach (DataRow dr in data.Rows)
            {
                var pair = GetType(sheetName);
                string sel_Sql = @"select " + pair.Value +
            "结果 from (select (case when 子项目 is null or len(子项目)<=0 then 项目 else 项目+'-'+子项目 end) 项目,结果  from getProductTestTab({0}) ) tt where tt.项目 is not null";
                sel_Sql = string.Format(sel_Sql, dr["样品序号"].ToString());
                var dict = DBUtil.DBUtil.GetDict<string, string>(db.QueryTable(sel_Sql, 2000), "项目", "结果");
                //if (dict.Count == 7)
                {
                    foreach (var item in dict)
                    {
                        //decimal dml;
                        //if (decimal.TryParse(item.Value, out dml))
                        //{
                        //    dr[item.Key] = dml;
                        //    dataNew.Rows[j][item.Key] = dml;
                        //}
                        //else
                        if (dataNew.Columns.IndexOf(item.Key) > -1)
                        {
                            dr[item.Key] = item.Value;
                            dataNew.Rows[j][item.Key] = item.Value;
                        }
                    }
                }
                //else
                {
                    //lstIndex.Add(j);
                }
                j++;
            }

            foreach (var index in lstIndex)
            {
                lstIndex.Remove(j);
            }


            dataNew.TableName = sheetName;

            return dataNew;
        }

    }
}
